"""
Case Type   : Ustore-Consistency
Case Name   : 开启事务,执行deleteA-rollback与deleteA-rollback并发,deleteA-commit与deleteA-commit并发,重启集群后执行sql命令
Create At   : 2024.10
Owner       : haomeng802
Description :
    1.建表1并插入数据
    2.创建表2,为表2创建索引
    3.向表2中插入数据
    4.线程1开启事务,设置事务隔离级别,新建保存点,删除表2数据后回滚事务到新建保存点,回滚事务
    5.线程2开启事务,新建保存点,设置事务隔离级别,再次新建保存点,在线程1删除表2数据的同时删除表2数据后提交事务
    6.查询表2数据
    7.线程1开启事务,设置事务隔离级别,新建保存点,删除表2数据后提交事务
    8.线程2开启事务,新建保存点,设置事务隔离级别,再次新建保存点,在线程1删除表2数据的同时删除表2数据后提交事务
    9.查询表2数据
    10.创建表3并插入数据
    11.重启数据库
    12.联合查询表数据
    13.开启事务,设置事务隔离级别,新建保存点,修改表2数据,删除表2数据后查询表2数据,回滚事务到保存点后提交事务
    14.查询表2数据
    15.清理环境
Expect      :
    "1.建表1并插入数据成功
    2.创建表2成功,为表2创建索引成功
    3.向表2中插入数据成功
    4.线程1开启事务成功,设置事务隔离级别成功,新建保存点成功,删除表2数据成功,
      回滚事务到新建保存点成功,回滚事务成功
    5.线程2开启事务成功,新建保存点成功,设置事务隔离级别成功,新建保存点成功,
      删除表2数据成功,回滚事务成功
    6.返回查询到的数据
    7.线程1开启事务成功,设置事务隔离级别成功,新建保存点成功,删除表2数据成功,
      提交事务成功
    8.线程2开启事务成功,新建保存点成功,设置事务隔离级别成功,新建保存点成功,
      删除表2数据成功,提交事务成功
    9.返回查询到的数据
    10.创建表3成功,向表3中插入数据成功
    11.重启数据库成功
    12.返回查询到的数据
    13.开启事务成功,设置事务隔离级别成功,新建保存点成功,修改表2数据成功,
       删除表2数据成功,返回查询到的表2数据,回滚事务到保存点成功,提交事务成功
    14.返回查询到的数据
    15.清理环境成功
History     :
"""

import os
import time
import unittest
from testcase.utils.Logger import Logger
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.ComThread import ComThread


class CONSISTENCY(unittest.TestCase):
    def setUp(self):
        self.pri_sh = CommonSH('PrimaryDbUser')
        self.constant = Constant()
        self.log = Logger()
        self.table1 = 's_ustore_consistency_01.t_consistency_0200_01'
        self.table2 = 's_ustore_consistency_01.t_consistency_0200_02'
        self.index = 's_ustore_consistency_01.i_consistency_0200'
        self.table3 = 's_ustore_consistency_01.t_consistency_0200_03'
        self.table4 = 's_ustore_consistency_02.t_ustore_consistency_0001_04'
        self.table5 = 's_ustore_consistency_02.t_ustore_consistency_0001_05'

    def test_Consistency(self):
        self.log.info(f'-----{os.path.basename(__file__)} start-----')
        text1 = '-----step1:建表1并向表1中插入数据  expect:建表1成功,向表1中插入数据成功-----'
        self.log.info(text1)

        create_table = self.pri_sh.execut_db_sql(
            f'''drop table if exists {self.table1};
                create table {self.table1}(c_int int) with (storage_type=ustore);            
                insert into {self.table1} values(0);''')
        self.log.info(create_table)
        self.assertIn(self.constant.TABLE_DROP_SUCCESS, create_table, '执行失败' + text1)
        self.assertIn(self.constant.TABLE_CREATE_SUCCESS, create_table, '执行失败' + text1)
        self.assertIn(self.constant.INSERT_SUCCESS_MSG, create_table, '执行失败' + text1)

        text2 = '-----step2:创建表2,为表2创建索引  expect:创建表2成功,为表2创建索引成功-----'
        self.log.info(text2)
        create_table = self.pri_sh.execut_db_sql(
            f'''drop table if exists {self.table2};
                create table {self.table2}(c_id int,c_d_id int not null,            
                c_w_id int not null ,c_first varchar(16) not null,c_middle char(2),            
                c_last varchar(16) not null,c_street_1 varchar(20) not null,            
                c_street_2 varchar(20),c_city varchar(20) not null,c_state char(2) not null,
                c_zip char(9) not null,c_phone char(16) not null,            
                c_since timestamp,c_credit char(2) not null,c_credit_lim number,            
                c_discount number,c_balance number,c_ytd_payment number not null,            
                c_payment_cnt int not null,c_delivery_cnt int not null,            
                c_data clob not null) with (storage_type=ustore);            
                create unique index {self.index} on {self.table2}(c_id,c_d_id,c_w_id);''')
        self.log.info(create_table)
        self.assertIn(self.constant.TABLE_DROP_SUCCESS, create_table, '执行失败' + text2)
        self.assertIn(self.constant.TABLE_CREATE_SUCCESS, create_table, '执行失败' + text2)
        self.assertIn(self.constant.CREATE_INDEX_SUCCESS_MSG, create_table, '执行失败' + text2)

        text3 = '-----step3:向表2中插入数据 expect:向表2中插入数据成功-----'
        self.log.info(text3)

        insert_data = self.pri_sh.execut_db_sql(
            f'''insert into {self.table2} select c_id,c_d_id,c_w_id,'aaaaa',
                'ab','dafkldafkadbfeod','dafkldafkadbfeodefgh',            
                'dafkldafkadbfeodefgh','dafkldafkadbfeodefgh','xy','acgfdagfs',            
                'dafkldafkadbfeod',null,'ad',50000.0,0.4491328,-10.0,10.0,1,0,            
                lpad('qraogfdamjkfdlhgmdjf',4000,'qraogfdamjkfdlhgmdjf') from {self.table4};''')
        self.log.info(insert_data)
        self.assertIn(self.constant.INSERT_SUCCESS_MSG, insert_data, '执行失败' + text3)

        text4 = '-----step4:线程1开启事务,设置事务隔离级别,新建保存点,删除表2数据后回滚事务到新建保存点,回滚事务  ' \
                'expect:线程1开启事务成功,设置事务隔离级别成功,新建保存点成功,删除表2数据成功,回滚事务到新建保存点成功,回滚事务成功-----'
        self.log.info(text4)

        start_trans = f'''start transaction;            
            set transaction isolation level read committed;            
            savepoint aa;            
            delete from {self.table2} where c_first='aaaaa';            
            select pg_sleep(10);            
            rollback to savepoint aa;            
            rollback;'''
        session1 = ComThread(self.pri_sh.execut_db_sql, args=(start_trans,))
        session1.setDaemon(True)

        text5 = '-----step5:线程2开启事务,新建保存点,设置事务隔离级别,再次新建保存点,在线程1删除表2数据的同时删除表2数据后回滚事务到新建保存点,回滚事务  ' \
                'expect:线程2开启事务成功,新建保存点成功,设置事务隔离级别成功,新建保存点成功,删除表2数据成功,回滚事务到新建保存点成功,回滚事务成功-----'
        self.log.info(text5)

        start_trans = f'''start transaction;            
            savepoint aa;            
            set transaction isolation level read committed;            
            savepoint aa;            
            delete from {self.table2} where c_first='aaaaa';            
            rollback to savepoint aa;            
            rollback;'''
        session2 = ComThread(self.pri_sh.execut_db_sql, args=(start_trans,))
        session2.setDaemon(True)

        session1.start()
        time.sleep(2)
        session2.start()
        session1.join(18)
        session2.join(5)

        result1 = session1.get_result()
        self.log.info(result1)
        result2 = session2.get_result()
        self.log.info(result2)
        self.assertIn(self.constant.START_TRANSACTION_SUCCESS_MSG, result1, '开启事务失败' + text4)
        self.assertIn('SET', result1, '设置事务级别失败' + text4)
        self.assertIn('SAVEPOINT', result1, '新建保存点失败' + text4)
        self.assertIn('DELETE', result1, '删除表2失败' + text4)
        self.assertEqual(result1.count('ROLLBACK'), 2, '执行失败' + text4)
        self.assertIn(self.constant.START_TRANSACTION_SUCCESS_MSG, result2, '开启事务失败' + text5)
        self.assertEqual(result2.count('SAVEPOINT'), 2, '新建保存点失败' + text5)
        self.assertIn('SET', result2, '设置事务级别失败' + text5)
        self.assertIn('DELETE', result2, '删除表2失败' + text5)
        self.assertEqual(result2.count('ROLLBACK'), 2, '执行失败' + text5)

        text6 = '-----step6:查询表2数据 expect:返回查询到的数据-----'
        self.log.info(text6)

        select_data = self.pri_sh.execut_db_sql(
            f'''select distinct(c_first),count(c_id) from {self.table2} 
                where c_first='aaaaa' group by c_first order by c_first;''')
        self.log.info(select_data)
        self.assertIn('aaaaa   |  1000', select_data, '执行失败' + text6)

        text7 = '-----step7:线程1开启事务,设置事务隔离级别,新建保存点,删除表2数据后提交事务  ' \
                'expect:线程1开启事务成功,设置事务隔离级别成功,新建保存点成功,删除表2数据成功,提交事务成功-----'
        self.log.info(text7)

        start_trans = f'''start transaction;            
            set transaction isolation level read committed;            
            savepoint aa;           
            delete from {self.table2} where mod(c_id,2)=0;            
            select pg_sleep(10);            
            commit;'''
        session1 = ComThread(self.pri_sh.execut_db_sql, args=(start_trans,))
        session1.setDaemon(True)

        text8 = '-----step8:线程2开启事务,新建保存点,设置事务隔离级别,再次新建保存点,在线程1删除表2数据的同时删除表2数据后提交事务  ' \
                'expect:线程2开启事务成功,新建保存点成功,设置事务隔离级别成功,新建保存点成功,删除表2数据成功,提交事务成功-----'
        self.log.info(text8)

        start_trans = f'''start transaction;		
            savepoint aa;            
            set transaction isolation level read committed;            
            savepoint aa;            
            delete from {self.table2} where mod(c_id,2)=0;            
            commit;'''
        session2 = ComThread(self.pri_sh.execut_db_sql, args=(start_trans,))
        session2.setDaemon(True)

        session1.start()
        time.sleep(2)
        session2.start()
        session1.join(18)
        session2.join(5)
        result1 = session1.get_result()
        self.log.info(result1)
        result2 = session2.get_result()
        self.log.info(result2)
        self.assertIn(self.constant.START_TRANSACTION_SUCCESS_MSG, result1, '开启事务失败' + text7)
        self.assertIn('SET', result1, '设置事务级别失败' + text7)
        self.assertIn('SAVEPOINT', result1, '新建保存点失败' + text7)
        self.assertIn('DELETE', result1, '删除表2失败' + text7)
        self.assertIn('COMMIT', result1, '提交事务失败' + text7)
        self.assertIn(self.constant.START_TRANSACTION_SUCCESS_MSG, result2, '开启事务失败' + text8)
        self.assertEqual(result2.count('SAVEPOINT'), 2, '新建保存点失败' + text8)
        self.assertIn('SET', result2, '设置事务级别失败' + text8)
        self.assertIn('DELETE', result2, '删除表2失败' + text8)
        self.assertIn('COMMIT', result2, '提交事务失败' + text8)

        text9 = '-----step9:查询表2数据 expect:返回查询到的数据-----'
        self.log.info(text9)

        select_data = self.pri_sh.execut_db_sql(
            f'''select distinct(c_first),count(c_id) from {self.table2} 
            where c_first='aaaaa' group by c_first order by c_first;''')
        self.log.info(select_data)
        self.assertIn('aaaaa   |   500', select_data, '执行失败' + text9)

        text10 = 'step10:创建表3并插入数据 expect:创建表3成功,插入数据成功'
        self.log.info(text10)
        create_table = self.pri_sh.execut_db_sql(
            f'''drop table if exists {self.table3};
                create table {self.table3}(c_id int,c_d_id int,c_w_id int,           
                c_first varchar(16),c_middle char(2),c_last varchar(16),            
                c_street_1 varchar(20),c_street_2 varchar(20),c_city varchar(20),           
                c_state char(2),c_zip char(9),c_phone char(16),c_since timestamp,            
                c_credit char(2),c_credit_lim number,c_discount number,c_balance number,
                c_ytd_payment number,c_payment_cnt int,c_delivery_cnt int,c_data clob);            
                insert into {self.table3} select * from {self.table2};''')
        self.log.info(create_table)
        self.assertIn(self.constant.TABLE_DROP_SUCCESS, create_table, '执行失败' + text10)
        self.assertIn(self.constant.TABLE_CREATE_SUCCESS, create_table, '执行失败' + text10)
        self.assertIn(self.constant.INSERT_SUCCESS_MSG, insert_data, '执行失败' + text10)

        text11 = '-----step11:重启数据库 expect:重启数据库成功-----'
        self.log.info(text11)

        msg = self.pri_sh.restart_db_cluster()
        self.log.info(msg)
        self.assertTrue(msg, '执行失败' + text11)

        text12 = '-----step12:结合键字minus联合查询表数据 expect:返回查询到的数据-----'
        self.log.info(text12)

        select_data = self.pri_sh.execut_db_sql(
            f'''select distinct(c_first),count(c_id) from {self.table2} 
                where c_first='aaaaa' group by c_first order by c_first;            
                select c_id,c_d_id,c_w_id,c_first,c_middle,c_last,c_street_1,            
                c_street_2,c_city,c_state, c_zip,c_phone,c_since,c_credit,            
                c_credit_lim,c_discount,c_balance,c_ytd_payment,c_payment_cnt,            
                c_delivery_cnt from {self.table2} minus select c_id,c_d_id,            
                c_w_id,c_first,c_middle,c_last,c_street_1,c_street_2,c_city,            
                c_state,c_zip,c_phone,c_since,c_credit,c_credit_lim,c_discount,            
                c_balance,c_ytd_payment,c_payment_cnt,c_delivery_cnt from {self.table3};
                select c_id,c_d_id,c_w_id,c_first,c_middle,c_last,c_street_1,            
                c_street_2,c_city,c_state,c_zip,c_phone,c_since,c_credit,            
                c_credit_lim,c_discount,c_balance,c_ytd_payment,c_payment_cnt,            
                c_delivery_cnt from {self.table3} minus select c_id,            
                c_d_id,c_w_id,c_first,c_middle,c_last,c_street_1,c_street_2,            
                c_city,c_state,c_zip,c_phone,c_since,c_credit,            
                c_credit_lim,c_discount,c_balance,c_ytd_payment,c_payment_cnt,            
                c_delivery_cnt from {self.table2};''')
        self.log.info(select_data)
        self.assertIn('aaaaa   |   500', select_data, '执行失败' + text9)
        self.assertEqual(select_data.count('(0 rows)'), 2, '执行失败' + text9)

        text13 = '-----step13:开启事务,设置事务隔离级别,新建保存点,修改表2数据,删除表2数据后查询表2数据,回滚事务到保存点后提交事务  ' \
                 'expect:开启事务成功,设置事务隔离级别成功,新建保存点成功,修改表2数据成功,删除表2数据成功,返回查询到的表2数据,回滚事务到保存点成功,提交事务成功-----'
        self.log.info(text13)

        start_trans = self.pri_sh.execut_db_sql(
            f'''start transaction;            
                set transaction isolation level read committed;            
                savepoint aa;            
                update {self.table2} set c_id=c_id+10000,c_d_id=c_d_id+10000,            
                c_w_id=c_w_id+10000,c_first=c_first||'bb',c_data='qraogfdamjkfdlhgmdjf';            
                delete from {self.table2} where mod(c_id,2)=0;            
                select distinct c_first,count(c_id),sum(c_id) from {self.table2} 
                where c_first='aaaaa' or c_first='aaaaabb' group by c_first order by c_first;            
                rollback to savepoint aa;            
                commit;''')
        self.log.info(start_trans)
        self.assertIn(self.constant.START_TRANSACTION_SUCCESS_MSG, start_trans, '开启事务失败' + text13)
        self.assertIn('SET', start_trans, '设置事务级别失败' + text13)
        self.assertIn(self.constant.UPDATE_SUCCESS_MSG, start_trans, '修改表2数据失败' + text13)
        self.assertIn('SAVEPOINT', start_trans, '新建保存点失败' + text13)
        self.assertIn('DELETE', start_trans, '删除表2数据失败' + text13)
        self.assertIn('aaaaabb |   500 | 5250000', start_trans, '结果错误' + text13)
        self.assertIn('ROLLBACK', start_trans, '回滚到新建保存点失败' + text13)
        self.assertIn('COMMIT',		start_trans, '提交失败' + text13)

        text14 = '-----step14:查询表2数据 expect:返回查询到的数据-----'
        self.log.info(text14)

        select_data = self.pri_sh.execut_db_sql(
            f'''select distinct c_first,count(c_id),sum(c_id) from {self.table2} 
                where c_first='aaaaa' or c_first='aaaaabb' group by c_first order by c_first;''')
        self.log.info(select_data)
        self.assertIn('aaaaa   |   500 | 250000', select_data, '执行失败' + text14)

    def tearDown(self):
        text15 = '-----step15:清理环境 expect:清理环境成功-----'
        self.log.info(text15)

        clean_environment = self.pri_sh.execut_db_sql(
            f'''drop index {self.index};
                drop table {self.table1};
                drop table {self.table2};
                drop table {self.table3};''')
        self.log.info(clean_environment)
        self.assertEqual(clean_environment.count(self.constant.TABLE_DROP_SUCCESS), 3, '执行失败' + text15)
        self.assertIn(self.constant.DROP_INDEX_SUCCESS_MSG, clean_environment, '执行失败' + text15)
        self.log.info(f'-----{os.path.basename(__file__)} end-----')
